#include "userdb.h"
#include <QDebug>
#include <QString>
#include <QDateTime>
UserDB &UserDB::instance()
{
    static UserDB _db;
    return _db;
}

bool UserDB::userLogin(QString id, QString password)
{
    QSqlQuery query(db);
    int _id = id.toInt();
    QString sql = "SELECT * FROM USER_INFO WHERE ID = ? AND PASSWORD = ?;";
    query.prepare(sql);
    query.addBindValue(_id);
    query.addBindValue(password);
    if (!query.exec()) {
        qDebug() << "执行失败" << db.lastError().text();
        return false;
    }
    if (query.next()) {
        qDebug() << "登录成功";
        return true;
    }
    qDebug() << "登录失败";
    return false;
}

bool UserDB::userRegister(QString id, QString password)
{
    int _id = id.toInt();
    QSqlQuery query(db);
    query.clear();
    QString sql = "INSERT INTO USER_INFO(ID, PASSWORD) VALUES(?, ?);";
    query.prepare(sql);
    query.addBindValue(_id);
    query.addBindValue(password);
    if (!query.exec()) {
        qDebug() << "执行失败" << db.lastError().text();
        return false;
    }
    return true;
}

bool UserDB::userUpdateInfo(QString name, QString id, QString old_password, QString new_password, QString tel, QString addr)
{
    if (!this->userLogin(id, old_password)) {
        qDebug() << "密码错误";
        return false;
    }
    int _id = id.toInt();
    QSqlQuery query(db);
    QString sql = "UPDATE USER_INFO SET USERNAME = ?, PASSWORD = ?, TEL = ?, ADDR = ? WHERE ID = ?;";
    query.prepare(sql);
    query.addBindValue(name);
    query.addBindValue(new_password);
    query.addBindValue(tel);
    query.addBindValue(addr);
    query.addBindValue(_id);
    if (!query.exec()) {
        qDebug() << "执行失败";
        return false;
    }
    qDebug() << "修改成功";
    return true;
}

/**
 * @brief UserDB::getNameByFeature
 * 根据人脸的特征值在数据库中一一匹配到最佳的人脸
 * @param feature
 * @param faceRecongnizer
 * @return 返回匹配成功的名字,否则返回NULL
 */
QString UserDB::getNameByFeature(cv::Mat feature, cv::Ptr<cv::FaceRecognizerSF> faceRecongnizer)
{
    QString name = "NULL";
    QString sql = "SELECT FEATURE, USERNAME FROM USER_INFO";
    QSqlQuery query(db);
    if (!query.exec(sql)) {
        qDebug() << "执行失败" << db.lastError().text();
        return name;
    }
    double cos_score = 0;
    while (query.next()) {
        QString _name = query.value("USERNAME").toString();
        QByteArray byteArray = query.value("FEATURE").toByteArray();
        if (byteArray.isEmpty()) {
            continue;
        }
        std::vector<float> _feature = deSerialVector<float>(byteArray);
        cv::Mat _feature_mat{1, 128, CV_32F, _feature.data()};
        double score = faceRecongnizer->match(feature, _feature_mat);
        if (score >= 0.363) {
            if (cos_score < score) {
                cos_score = score;
                name = _name;
            }
        }
    }
    return name;
}

/**
 * @brief UserDB::insertFeatureByID
 * 根据用户的id更新人脸的特征值,
 * @param id
 * @param feature
 * @return 成功返回true,失败返回false
 */
bool UserDB::insertFeatureByID(int id, cv::Mat& feature)
{
    QSqlQuery query(db);
    QString sql = "UPDATE USER_INFO SET FEATURE = ? WHERE ID = ?;";
    query.prepare(sql);
    std::vector<float> _feature = feature;
    query.addBindValue(serialVector<float>(_feature));
    query.addBindValue(id);
    if (!query.exec()) {
        qDebug() << "插入人脸数据失败" << db.lastError().text();
        return false;
    }
    return true;
}

bool UserDB::UserClockin(int id)
{
    QDateTime date = QDateTime::currentDateTime();
    QString _date = date.toString("yyyy-MM-dd hh:mm:ss");
    qDebug() << _date;
    QSqlQuery query(db);
    QString sql = "INSERT INTO USER_CLOCK_IN VALUES(?, ?);";
    query.prepare(sql);
    query.addBindValue(id);
    query.addBindValue(_date);
    if (!query.exec()) {
        qDebug() << "执行插入失败" << db.lastError().text();
        return false;
    }
    return true;
}

QSqlDatabase &UserDB::getHandle()
{
    return this->db;
}

UserDB::UserDB() {
    db = QSqlDatabase::addDatabase("QSQLITE");
//    windows
//    db.setDatabaseName("../../userdb.db");
    db.setDatabaseName("./userdb.db");
    if (!db.open()) {
        qDebug() << "创建数据库失败";
        throw std::runtime_error{"create database failed"};
    }
    QString sql;
    sql = "CREATE TABLE IF NOT EXISTS 'USER_INFO'("
                                                    "ID INTEGER PRIMARY KEY, "
                                                    "USERNAME TEXT, "
                                                    "PASSWORD TEXT NOT NULL, "
                                                    "TEL INTEGER, "
                                                    "IDCARD INTEGER, "
                                                    "ADDR TEXT, "
                                                    "FEATURE BLOB);";
    QSqlQuery query(db);
    if (!query.exec(sql)) {
        qDebug() << "创建用户信息表失败";
        throw std::runtime_error{"create userinfo table failed"};
    }
    sql = "CREATE TABLE IF NOT EXISTS 'USER_CLOCK_IN'("
                                                        "ID INTEGER, "
                                                        "TEXT CLOCK_TIME, "
                                                        "FOREIGN KEY(ID) REFERENCES USER_INFO(ID));";
    if (!query.exec(sql)) {
        qDebug() << "创建用户考勤表失败";
        throw std::runtime_error{"create USER_CLOCK_IN table failed"};
    }
    qDebug() << "初始化数据库成功";
}
